IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Comment')
BEGIN
  DROP TABLE Comment
END

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'ContributionTag')
BEGIN
  DROP TABLE ContributionTag
END

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Contribution')
BEGIN
  DROP TABLE Contribution
END

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Member')
BEGIN
  DROP TABLE Member
END

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Tag')
BEGIN
  DROP TABLE Tag
END

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'ContentType')
BEGIN
  DROP TABLE ContentType
END

CREATE TABLE Member
(
	Id               INT            NOT NULL PRIMARY KEY IDENTITY,
	Email            NVARCHAR(100)  NOT NULL UNIQUE,
	PasswordHash     NVARCHAR(40)   NOT NULL,
	LastActiveDate   DATETIME       NOT NULL,
	LastUpdatedDate  DATETIME       NOT NULL,
	Locked           BIT            NOT NULL DEFAULT 0,
	Username         NVARCHAR(50)   NOT NULL,
	IsModerator      BIT            NOT NULL DEFAULT 0,
	HashCookie       NVARCHAR(40)       NULL
)

CREATE TABLE Tag
(
	Id           INT          NOT NULL PRIMARY KEY IDENTITY,
	Value        NVARCHAR(50) NOT NULL,
	LoweredValue NVARCHAR(50) NOT NULL,
	Usages       INT          NOT NULL DEFAULT 1
)

CREATE TABLE ContentType
(
	Id          INT          NOT NULL PRIMARY KEY IDENTITY,
	Description NVARCHAR(50) NOT NULL
)

CREATE TABLE Contribution
(
	Id                      INT             NOT NULL PRIMARY KEY IDENTITY,
	ContributorId           INT             NOT NULL FOREIGN KEY REFERENCES Member(Id),
	ApprovedById            INT                 NULL FOREIGN KEY REFERENCES Member(Id),
	ContentTypeId           INT             NOT NULL FOREIGN KEY REFERENCES ContentType(Id),
	Title                   NVARCHAR(50)    NOT NULL,
	Description             NVARCHAR(255)   NOT NULL,
	Views                   INT             NOT NULL DEFAULT 0,
	Downloads               INT             NOT NULL DEFAULT 0,
	RatingTotal             INT             NOT NULL DEFAULT 0,
	RatingCount             INT             NOT NULL DEFAULT 0,
	AddedOn                 DATETIME        NOT NULL,
	PreviewImage            NVARCHAR(1024)      NULL,
	PreviewLink             NVARCHAR(1024)  NOT NULL,
	DownloadLink            NVARCHAR(1024)  NOT NULL,
	DownloadExperienceCount INT             NOT NULL DEFAULT 0,
	DownloadExperienceTotal INT             NOT NULL DEFAULT 0,
	GeoX                    DECIMAL(24, 18)     NULL,
	GeoY                    DECIMAL(24, 18)     NULL
)

CREATE TABLE ContributionTag
(
	Id             INT NOT NULL PRIMARY KEY IDENTITY,
	ContributionId INT NOT NULL FOREIGN KEY REFERENCES Contribution(Id),
	TagId          INT NOT NULL FOREIGN KEY REFERENCES Tag(Id)
)

CREATE TABLE Comment
(
	Id             INT           NOT NULL PRIMARY KEY IDENTITY,
	ContributionId INT           NOT NULL FOREIGN KEY REFERENCES Contribution(Id),
	MemberId       INT           NOT NULL FOREIGN KEY REFERENCES Member(Id),
	Subject        NVARCHAR(128) NOT NULL,
	Message        NTEXT         NOT NULL,
	PostedOn       DATETIME      NOT NULL
) 